package com.vegas.netspider.db;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import org.springframework.jdbc.core.PreparedStatementSetter;
import org.springframework.jdbc.core.RowMapper;

import com.vegas.netspider.dto.SportMatch;

public class SportMatchDbHandler extends DbHandler{

	private String ft_insert_sql = "insert into ft_match(match_id,league_name,home_team_name,home_team_id,guest_team_name,guest_team_id,play_time,will_play,running_time,current_ht_score,current_gt_score,over_tag) " +
			"values(?,?,?,?,?,?,?,?,?,?,?,?)";
	public void saveFt(final SportMatch m){
		if(m==null||m.getMatchId()==null){
			return;
		}
		
		boolean exists = this.existsFt(m.getMatchId());
		if(exists){
			return;
		}
		
		jdbcTemplate.update(ft_insert_sql,new PreparedStatementSetter(){
			@Override
			public void setValues(PreparedStatement p) throws SQLException {
				p.setInt(1, m.getMatchId());
				p.setString(2, m.getLeagueName());
				p.setString(3, m.getHomeTeamName());
				p.setInt(4, m.getHomeTeamId());
				p.setString(5, m.getGuestTeamName());
				p.setInt(6, m.getGuestTeamId());
				p.setLong(7, m.getPlayTime());
				p.setBoolean(8, m.isWillPlay());
				p.setString(9, m.getRunningTime());
				p.setInt(10, m.getCurrentHtScore()==null?0:m.getCurrentHtScore());
				p.setInt(11, m.getCurrentGtScore()==null?0:m.getCurrentGtScore());
				p.setBoolean(12, false);
			}
		});
	}
	
	private String existsFtSql="select match_id from ft_match where match_id=? limit 1";
	public boolean existsFt(Integer matchId){
		List<Integer> result = jdbcTemplate.query(existsFtSql,new Object[]{matchId},new RowMapper<Integer>(){
			@Override
			public Integer mapRow(ResultSet r, int arg1) throws SQLException {
				// TODO Auto-generated method stub
				return r.getInt("match_id");
			}
			
		});
		
		
		if(result==null||result.isEmpty()||result.get(0)==null||result.get(0)<1){
			return false;
		}
		
		return true;
	}
	
	
	private String existsBkSql="select match_id from bk_match where match_id=? limit 1";
	public boolean existsBk(Integer matchId){
		List<Integer> result = jdbcTemplate.query(existsBkSql,new Object[]{matchId},new RowMapper<Integer>(){
			@Override
			public Integer mapRow(ResultSet r, int arg1) throws SQLException {
				// TODO Auto-generated method stub
				return r.getInt("match_id");
			}
			
		});
		
		
		if(result==null||result.isEmpty()||result.get(0)==null||result.get(0)<1){
			return false;
		}
		
		return true;
	}
	
	private String bk_insert_sql = "insert into bk_match(match_id,league_name,home_team_name,home_team_id,guest_team_name,guest_team_id,play_time,will_play,running_time,current_ht_score,current_gt_score,over_tag) " +
			"values(?,?,?,?,?,?,?,?,?,?,?,?)";
	public void saveBk(final SportMatch m){
		if(m==null||m.getMatchId()==null){
			return;
		}
		
		boolean exists = this.existsBk(m.getMatchId());
		if(exists){
			return;
		}
		
		jdbcTemplate.update(bk_insert_sql,new PreparedStatementSetter(){
			@Override
			public void setValues(PreparedStatement p) throws SQLException {
				p.setInt(1, m.getMatchId());
				p.setString(2, m.getLeagueName());
				p.setString(3, m.getHomeTeamName());
				p.setInt(4, m.getHomeTeamId());
				p.setString(5, m.getGuestTeamName());
				p.setInt(6, m.getGuestTeamId());
				p.setLong(7, m.getPlayTime());
				p.setBoolean(8, m.isWillPlay());
				p.setString(9, m.getRunningTime());
				p.setInt(10, m.getCurrentHtScore()==null?0:m.getCurrentHtScore());
				p.setInt(11, m.getCurrentGtScore()==null?0:m.getCurrentGtScore());
				p.setBoolean(12, false);
			}
		});
	}
	
	
	private String ft_over_tag="update ft_match set over_tag=? where home_team_id=? and guest_team_id=? and play_time>? and play_time<?";
	public void setFtMatchOver(final Integer htId,final Integer gtId,final long playTime){
		if(htId==null||gtId==null){
			return;
		}
		
		jdbcTemplate.update(ft_over_tag,new PreparedStatementSetter(){
			@Override
			public void setValues(PreparedStatement p) throws SQLException {
				p.setBoolean(0, true);
				p.setInt(1, htId);
				p.setInt(2, gtId);
				p.setLong(3, playTime-24*60*60*1000);
				p.setLong(4, playTime+12*60*60*1000);
			}
			
		});
	}
	
	
	private String bk_over_tag="update bk_match set over_tag=? where home_team_id=? and guest_team_id=? and play_time>? and play_time<=?";
	public void setBkMatchOver(final Integer htId,final Integer gtId,final long playTime){
		if(htId==null||gtId==null){
			return;
		}
		
		jdbcTemplate.update(bk_over_tag,new PreparedStatementSetter(){
			@Override
			public void setValues(PreparedStatement p) throws SQLException {
				p.setBoolean(1, true);
				p.setInt(2, htId);
				p.setInt(3, gtId);
				p.setLong(4, playTime-24*60*60*1000);
				p.setLong(5, playTime+12*60*60*1000);
			}
			
		});
	}
	
	private String ft_list="select match_id,league_name from ft_match where home_team_id=? and guest_team_id=? and play_time>? and play_time<?";
	public List<SportMatch> listFt(final Integer hId,final Integer gId,final long playTime){
		return jdbcTemplate.query(ft_list,new PreparedStatementSetter(){

			@Override
			public void setValues(PreparedStatement p) throws SQLException {
				p.setInt(1, hId);
				p.setInt(2, gId);
				p.setLong(3, playTime-24*60*60*1000);
				p.setLong(4, playTime+12*60*60*1000);
			}
			
		}, new RowMapper<SportMatch>(){

			@Override
			public SportMatch mapRow(ResultSet r, int arg1)
					throws SQLException {
				SportMatch sm = new SportMatch();
				sm.setMatchId(r.getInt("match_id"));
				sm.setLeagueName(r.getString("league_name"));
				return sm;
			}
			
		});
	}
	
	
	private String bk_list="select match_id,league_name from bk_match where home_team_id=? and guest_team_id=? and play_time>? and play_time<?";
	public List<SportMatch> listBk(final Integer hId,final Integer gId,final long playTime){
		return jdbcTemplate.query(bk_list,new PreparedStatementSetter(){

			@Override
			public void setValues(PreparedStatement p) throws SQLException {
				p.setInt(1, hId);
				p.setInt(2, gId);
				p.setLong(3, playTime-24*60*60*1000);
				p.setLong(4, playTime+12*60*60*1000);
			}
			
		}, new RowMapper<SportMatch>(){

			@Override
			public SportMatch mapRow(ResultSet r, int arg1)
					throws SQLException {
				SportMatch sm = new SportMatch();
				sm.setMatchId(r.getInt("match_id"));
				sm.setLeagueName(r.getString("league_name"));
				return sm;
			}
			
		});
	}
	
}
